﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data.SqlClient;
using System.Data;

namespace mamnontuoitho7.AppCode
{
    public class KidWall
    {
        public ConnectGetData _connectData;
        public KidWall()
        {
            _connectData = new ConnectGetData();
        }

        /// <summary>
        /// Insert Kid Wall into database
        /// </summary>
        /// <param name="teacherID"></param>
        /// <param name="kidID"></param>
        /// <param name="content"></param>
        public bool KidWallCreate(String teacherID,String kidID,  String content)
        {
            SqlConnection con = _connectData.GetConnect();

            try
            {
                string sql = "insert KidWall values (@teacherID, @kidID, @content, default,0)";
                SqlCommand cmd = new SqlCommand(sql, con);
                cmd.Parameters.AddWithValue("@teacherID", teacherID);
                cmd.Parameters.AddWithValue("@kidID", kidID);
                cmd.Parameters.AddWithValue("@content", content);
                con.Open();
                cmd.ExecuteNonQuery();
                cmd.Dispose();
                con.Close();
                return true;
            }
            catch (Exception ex)
            {
                System.Console.WriteLine("Co loiiii  : " + ex.StackTrace);
                return false;
            }
        }

        /// <summary>
        /// Get Wall Information of a Kid
        /// </summary>
        /// <param name="_kidID"></param>
        /// <returns></returns>
        internal DataTable GetKidWall(String kidID)
        {
            SqlConnection con = _connectData.GetConnect();
            string sqlUp = "select * from KidWallView where KidID = @kidID order by KidWallID DESC";
            SqlCommand cmd = new SqlCommand(sqlUp, con);
            cmd.Parameters.AddWithValue("@kidID", kidID);

            return _connectData.GetDataTable(cmd);
        }


        /// <summary>
        /// Get a message of kid wall
        /// </summary>
        /// <param name="p"></param>
        internal DataRow GetOneRecordOfKidWall(String kidWallID)
        {
            SqlConnection con = _connectData.GetConnect();
            string sqlUp = "select * from KidWall where KidWallID = @kidWallID";
            SqlCommand cmd = new SqlCommand(sqlUp, con);
            cmd.Parameters.AddWithValue("@kidWallID", kidWallID);

            return _connectData.GetDataTable(cmd).Rows[0];
        }


        /// <summary>
        /// Update a Wall Message of a kid
        /// </summary>
        /// <param name="p"></param>
        /// <param name="p_2"></param>
        internal bool KidWallUpdate(String kidWallID, String teacherID, String content)
        {
            
            try
            {
                SqlConnection con = _connectData.GetConnect();
                string sql = "update KidWall set TeacherID=@teacherID, Content=@content where KidWallID=@kidWallID";
                SqlCommand cmd = new SqlCommand(sql, con);
                cmd.Parameters.AddWithValue("@kidWallID", kidWallID);
                cmd.Parameters.AddWithValue("@teacherID", teacherID);
                cmd.Parameters.AddWithValue("@content", content);

                con.Open();
                cmd.ExecuteNonQuery();
                cmd.Dispose();
                con.Close();
                return true;
            }
            catch (Exception ex)
            {
                System.Console.WriteLine("Co loiiii  : " + ex.StackTrace);
                return false;
            }
        }


        /// <summary>
        /// Delete wall kid.
        /// </summary>
        /// <param name="p"></param>
        internal void DeleteKidWall(String kidWallID)
        {
            SqlConnection con = _connectData.GetConnect();

            string sqlUp = "Delete KidWall " +
                                            " where KidWallID = @kidWallID ";

            SqlCommand cmd = new SqlCommand(sqlUp, con);
            cmd.Parameters.AddWithValue("@kidWallID", kidWallID);

            con.Open();
            cmd.ExecuteNonQuery();
            cmd.Dispose();
            con.Close();
        }

        /// <summary>
        /// get total number of wall of one kid
        /// </summary>
        /// <param name="articleTypeID"></param>
        /// <returns>int</returns>
        public global::System.Nullable<int> TotalWallOfOneKid(String kidID)
        {

            SqlConnection con = _connectData.GetConnect();

            con.Open();

            string sql = "SELECT COUNT(*) FROM KidWall WHERE  KidID ="+kidID;
            SqlCommand cmd = new SqlCommand(sql, con);

            object returnValue;

            returnValue = cmd.ExecuteScalar();
            con.Close();
            if (((returnValue == null) || (returnValue.GetType() == typeof(global::System.DBNull))))
            {
                return new global::System.Nullable<int>();
            }
            else
            {
                return new global::System.Nullable<int>(((int)(returnValue)));
            }
        }

        /// <summary>
        /// get a number of record of a page 
        /// </summary>
        /// <param name="pageIndex"></param>
        /// <param name="step"></param>
        /// <returns>datatable</returns>
        public DataTable GetWallPaged(int pageIndex, int step, String kidID)
        {
            //int startRowIndex = pageIndex * step + 1;
            //int endRowIndex = startRowIndex + step -1;
            int startRowIndex = (pageIndex - 1) * step + 1;
            int endRowIndex = startRowIndex + step - 1;
            string sql = "SELECT * FROM (SELECT  ROW_NUMBER() OVER(ORDER BY KidWallID DESC) AS number, * FROM KidWallView where KidID = "+kidID+" ) AS TempTable WHERE number BETWEEN @startRowIndex AND @endRowIndex";

            SqlConnection con = _connectData.GetConnect();
            SqlCommand cmd = new SqlCommand(sql, con);

            cmd.Parameters.AddWithValue("@startRowIndex", startRowIndex);
            cmd.Parameters.AddWithValue("@endRowIndex", endRowIndex);
            DataTable dt = _connectData.GetDataTable(cmd);
       
            return dt;
        }

        /// <summary>
        /// get total number of kidwall of kids for his/her parent
        /// </summary>
        /// <param name="articleTypeID"></param>
        /// <returns>int</returns>
        public global::System.Nullable<int> TotalUnseenWallOfKids(String parentID)
        {

            SqlConnection con = _connectData.GetConnect();

            con.Open();

            string sql = "select  COUNT(KidWallID) from  KidWallParentView  where  ParentID =" + parentID + " and IsRead = 0 ";
            SqlCommand cmd = new SqlCommand(sql, con);

            object returnValue;

            returnValue = cmd.ExecuteScalar();
            con.Close();
            if (((returnValue == null) || (returnValue.GetType() == typeof(global::System.DBNull))))
            {
                return new global::System.Nullable<int>();
            }
            else
            {
                return new global::System.Nullable<int>(((int)(returnValue)));
            }
        }


        /// <summary>
        /// get total number of kidwall of one kid for his/her parent
        /// </summary>
        /// <param name="articleTypeID"></param>
        /// <returns>int</returns>
        public global::System.Nullable<int> TotalUnseenWallOfOneKid(String kidID)
        {

            SqlConnection con = _connectData.GetConnect();

            con.Open();

            string sql = "select  COUNT(KidWallID) from  KidWallParentView  where  KidID =" + kidID + " and IsRead = 0 ";
            SqlCommand cmd = new SqlCommand(sql, con);

            object returnValue;

            returnValue = cmd.ExecuteScalar();
            con.Close();
            if (((returnValue == null) || (returnValue.GetType() == typeof(global::System.DBNull))))
            {
                return new global::System.Nullable<int>();
            }
            else
            {
                return new global::System.Nullable<int>(((int)(returnValue)));
            }
        }

        /// <summary>
        /// update wall status 
        /// </summary>
        /// <param name="productID"></param>
        /// <param name="status"></param>
        public void ChangeWallStatus(String parentID, String kidID)
        {
            SqlConnection con = _connectData.GetConnect();
            con.Open();
            string sql = "update KidWallParentView set IsRead =1 where ParentID =@ParentID and IsRead =0 and KidID=@KidID";
            SqlCommand cmd = new SqlCommand(sql, con);
            cmd.Parameters.AddWithValue("@ParentID", parentID);
            cmd.Parameters.AddWithValue("@KidID", kidID);
            cmd.ExecuteNonQuery();
            cmd.Dispose();
            con.Close();
        }

        /// <summary>
        /// check if the wall belong to the current username : teacher
        /// </summary>
        /// <param name="username"></param>
        /// <param name="commentID"></param>
        /// <returns>true if the current user is the owner</returns>
        public bool CheckWallOwner(String teacherID, String wallID)
        {
            bool flag = false;

            try
            {
                string sql = "select  * from KidWall where KidWallID = @KidWallID and TeacherID =@TeacherID ";

                SqlCommand cmd = new SqlCommand(sql, _connectData.GetConnect());
                cmd.Parameters.AddWithValue("@KidWallID", wallID);
                cmd.Parameters.AddWithValue("@TeacherID", teacherID);
                DataTable dt = _connectData.GetDataTable(cmd);

                if (dt.Rows.Count > 0)
                {
                    flag = true;
                }
                cmd.Dispose();
            }
            catch (Exception ex)
            {
                System.Console.WriteLine("Error error error: " + ex.StackTrace);
                return flag;
            }
            return flag;
        }

        /// <summary>
        /// delete wall by ID 
        /// </summary>
        /// <param name="NoticeID"></param>
        /// <returns></returns>
        public bool WallDelete(String wallID)
        {
            SqlConnection con = _connectData.GetConnect();
            con.Open();
            try
            {
                string sql = "delete from KidWall  where KidWallID = @KidWallID";
                SqlCommand cmd = new SqlCommand(sql, con);
                cmd.Parameters.AddWithValue("@KidWallID", wallID);

                cmd.ExecuteNonQuery();
                cmd.Dispose();
                con.Close();
            }
            catch (Exception ex)
            {
                System.Console.WriteLine("Co loiiii  : " + ex.StackTrace);
                return false;
            }
            return true;
        }




    }
}